package com.zhenyi.liuxue.core;

import java.io.File;
import java.util.List;

import com.cjw.model.DBMap;
import com.jfinal.kit.StrKit;
import com.liuxue.common.model.LxCertificate;
import com.liuxue.common.model.LxCnSchool;
import com.liuxue.common.model.LxCompany;
import com.liuxue.common.model.LxCompanySchool;
import com.liuxue.common.model.LxMajor;
import com.liuxue.common.model.LxMajorRankScore;
import com.liuxue.common.model.LxSchool;
import com.liuxue.common.model.LxSchoolMajor;
import com.liuxue.common.model.LxSchoolRank;
import com.liuxue.common.model.LxUkSchool;
import com.liuxue.common.model.LxUser;
import com.liuxue.common.model.LxUserCertificate;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

public class ImportUtils {

	public static final String[] MAJOR_ARRAY = { "Arts & Humanities", "Education", "Law", "Psychology", "Business",
			"Clinical", "Computer Science", "Engineering", "Life Sciences", "Natural Science", "Social Sciences" };

	private int getIndexOfMajors(String name) {
		for (int i = 0; i < MAJOR_ARRAY.length; i++) {
			String item = MAJOR_ARRAY[i];
			if (name.toLowerCase().contains(item.toLowerCase())) {
				return i;
			}
		}
		return -1;
	}

	/**
	 * 导入录取标准
	 * 
	 * @param file
	 * @param ukSchoolId
	 * @return true if success
	 */
	public boolean importSchoolMajor(File file, Long ukSchoolId) {
		// String path = "D:\\UserData\\Desktop\\工作簿1.xlsx";
		// ExcelReader reader = ExcelUtil.getReader(path);
		try {
			ExcelReader reader = ExcelUtil.getReader(file);
			List<List<Object>> list = reader.read();
			String prevMajor2 = "";
			for (int index = 0; index < list.size(); index++) {
				List<Object> row = list.get(index);
				if (index == 0) {
					// System.out.println("row.size():" + row.size());
					if (row.size() != 7) {
						System.out.println("栏数错误：确认是否为录取标准文件");
						return false;
					}
					continue;
				}
				if (row.size() < 6) {
					// System.out.println("cell missing row. jump next");
					continue;
				}
				String schoolName = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
				String major1 = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" : row.get(1)) : ""; // 专业大项
				String department = row.size() > 2 ? String.valueOf(row.get(2) == null ? "" : row.get(2)) : "";
				String major2 = row.size() > 3 ? String.valueOf(row.get(3) == null ? "" : row.get(3)) : ""; // 专业细类
				String rank = row.size() > 4 ? String.valueOf(row.get(4) == null ? "" : row.get(4)) : "";
				String scoreStr = row.size() > 5 ? String.valueOf(row.get(5) == null ? "" : row.get(5)) : "";
				String requirement = row.size() > 6 ? String.valueOf(row.get(6) == null ? "" : row.get(6)) : "";
				Integer isNoMore = 0;

				if (schoolName.startsWith("※")) {
					// System.out.println("remark row, ignored.");
					continue;
				}

				// System.out.println(index + ". ukSchool:" + schoolName + "\tmajor:" + major2 +
				// " department:" + department
				// + " ==========>");

				// if (StrKit.isBlank(major2) || major2.equals("null")) {
				// System.out.println("major empty, jump next.");
				// continue;
				// }
				if (StrKit.isBlank(department) && StrKit.isBlank(major2)) {
					// System.out.println("department & major name empty, next");
					continue;
				}
				// 学院不为空而专业细项为空 => 代表该学院全部专业
				if (StrKit.notBlank(department) && StrKit.isBlank(major2)) {
					major2 = "null";
					isNoMore = 1;
				}

				department = department.replace("\r\n", "");
				department = department.replace("\n", "");

				major2 = major2.replace("\r\n", "");
				major2 = major2.replace("\n", "");
				// System.out.println(major2);

				Integer score = null;
				String extra = "";
				if (StrKit.notBlank(scoreStr)) {
					if (!NumberUtil.isNumber(scoreStr)) {
						extra = scoreStr;
						score = 100;
					} else {
						score = (int) (Float.valueOf(scoreStr) * 100);
					}
				}
				// System.out.println("score: " + score + " extra: " + extra);
				Integer minRank = null;
				Integer maxRank = null;
				if (StrKit.notBlank(rank)) {
					minRank = Integer.valueOf(rank.split("#")[0]);
					maxRank = Integer.valueOf(rank.split("#")[1]);
				}
				// if (major2.equals(prevMajor2) && minRank != maxRank) {
				// minRank += 1;
				// }

				LxUkSchool school = LxUkSchool.dao.findById(ukSchoolId);
				// LxUkSchool school = LxUkSchool.dao.searchFirst("name", schoolName);
				if (!school.getName().equals(schoolName)) {
					System.out.println("英国大学名称不对应：请检查是否选择了正确的大学录取标准文件");
					return false;
				}
				// if (school == null) {
				// System.out.println("school not found, jump next");
				// continue;
				// }

				int type = getIndexOfMajors(major1);
				// System.out.println("--> type: " + type + " " + MAJOR_ARRAY[type]);

				DBMap map = new DBMap();
				map.put("schoolId", school.getId());
				map.put("department", department);
				map.put("name", major2);
				map.put("type", type);
				LxSchoolMajor schoolMajor = LxSchoolMajor.dao.searchFirst(map);

				if (schoolMajor == null) {
					// System.out.println("new major");
					schoolMajor = new LxSchoolMajor();
					schoolMajor.setSchoolId(school.getId());
					schoolMajor.setName(major2);
					schoolMajor.setScore(score);
					schoolMajor.setIntro(major2);
					schoolMajor.setDepartment(department);
					schoolMajor.setMinRank(maxRank); // minRank maxRank 混乱
					schoolMajor.setExtra(extra);
					if (type >= 0) {
						schoolMajor.setType(type);
					}
					schoolMajor.setIsNoMore(isNoMore);
					schoolMajor.save();
				}

				LxMajorRankScore majorRankScore = new LxMajorRankScore();
				majorRankScore.setMajorId(schoolMajor.getId());
				majorRankScore.setMinRank(minRank);
				majorRankScore.setMaxRank(maxRank);
				if (schoolMajor.getMinRank() < maxRank) {
					schoolMajor.setMinRank(maxRank);
					schoolMajor.update();
				}
				majorRankScore.setScore(score);
				if (schoolMajor.getScore() > score) {
					schoolMajor.setScore(score);
					schoolMajor.update();
				}
				majorRankScore.setExtra(extra);
				if (StrKit.notBlank(requirement)) {
					requirement = requirement.replace("\r\n", "<br />");
					requirement = requirement.replace("\n", "<br />");
					majorRankScore.setRequirement(requirement);
				}
				majorRankScore.save();

				prevMajor2 = major2;
			}
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 导入大学排名
	 * 
	 * @param file
	 * @param ukSchoolId
	 * @return true if success
	 */
	public boolean importSchoolRanking(File file, Long ukSchoolId) {
		// String path = "D:\\UserData\\Desktop\\工作簿3.xlsx";
		// ExcelReader reader = ExcelUtil.getReader(path);
		try {
			ExcelReader reader = ExcelUtil.getReader(file);
			List<List<Object>> list = reader.read();
			for (int index = 0; index < list.size(); index++) {
				List<Object> row = list.get(index);
				if (index == 0) {
					// System.out.println("row.size():" + row.size());
					if (row.size() != 3) {
						System.out.println("栏数错误：确认是否为大学排名文件");
						return false;
					}
					continue;
				}
				if (row.size() < 3) {
					// System.out.println("empty row, next");
					continue;
				}
				String ukSchoolName = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
				String cnSchoolName = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" : row.get(1)) : "";
				String rank = row.size() > 2 ? String.valueOf(row.get(2) == null ? "" : row.get(2)) : "";

				if (ukSchoolName.startsWith("※")) {
					// System.out.println("remark row, ignored.");
					continue;
				}

				// System.out.println(index + ". uk: " + ukSchoolName + " cn: " + cnSchoolName +
				// " ===========>");

				if (StrKit.isBlank(cnSchoolName)) {
					// System.out.println("cn school name empty, next");
					continue;
				}

				cnSchoolName = cnSchoolName.replace("\n", "").replace(" ", "");

				// LxUkSchool ukSchool = LxUkSchool.dao.searchFirst("name", ukSchoolName);
				LxUkSchool ukSchool = LxUkSchool.dao.findById(ukSchoolId);
				if (!ukSchool.getName().equals(ukSchoolName)) {
					System.out.println("英国大学名称不对应：请检查是否选择了正确的大学排名文件");
					return false;
				}
				LxCnSchool cnSchool = LxCnSchool.dao.searchFirst("name", cnSchoolName);
				if (cnSchool == null) {
					cnSchool = new LxCnSchool();
					cnSchool.setName(cnSchoolName);
					cnSchool.save();
				} else {
					DBMap map = new DBMap();
					map.put("cnSchoolId", cnSchool.getId());
					map.put("ukSchoolId", ukSchool.getId());
					LxSchoolRank sr = LxSchoolRank.dao.searchFirst(map);
					if (sr != null) {
						// System.out.println("cnSchool - ukSchool link exist, jump next.");
						continue;
					}
				}
				LxSchoolRank schoolRank = new LxSchoolRank();
				schoolRank.setCnSchoolId(cnSchool.getId());
				schoolRank.setUkSchoolId(ukSchool.getId());
				schoolRank.setScore(ukSchool.getScore());

				rank = rank.toLowerCase();
				if (rank.startsWith("top")) {
					schoolRank.setRank(1);
				} else if (rank.startsWith("over")) {
					rank = rank.replace("top", "").trim();
					schoolRank.setRank(Integer.valueOf(rank) + 1);
				} else if (rank.contains("-")) {
					String[] range = rank.split("-");
					schoolRank.setRank(Integer.valueOf(range[0]));
				} else {
					schoolRank.setRank(Integer.valueOf(rank));
				}

				schoolRank.save();
			}
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public void fixCnSchool() {
		List<LxCnSchool> wrongSchools = LxCnSchool.dao.find(
				"SELECT lx_cn_school.* FROM lx_cn_school WHERE lx_cn_school.`name` LIKE '%\\n%' OR lx_cn_school.`name` LIKE '% %'");
		int count = 0;
		for (LxCnSchool wrongItem : wrongSchools) {
			count++;
			System.out.println(count + " =======================");
			System.out.println(wrongItem.getId() + ": " + wrongItem.getName());
			String correctName = wrongItem.getName().replace("\n", "").replace(" ", "");
			System.out.println("Correct name: " + correctName);
			LxCnSchool correctItem = LxCnSchool.dao.searchFirst("name", correctName);
			if (correctItem != null) {
				System.out.println("Correct item ===> " + correctItem.getId() + ": " + correctItem.getName());
				correctItem.setName(correctName);
				correctItem.update();
				List<LxSchoolRank> wrongSchoolRanks = LxSchoolRank.dao.search("cnSchoolId", wrongItem.getId());
				for (LxSchoolRank wrongSchoolRank : wrongSchoolRanks) {
					DBMap map = new DBMap();
					map.put("ukSchoolId", wrongSchoolRank.getUkSchoolId());
					map.put("cnSchoolId", correctItem.getId());
					LxSchoolRank schoolRank = LxSchoolRank.dao.searchFirst(map);
					if (schoolRank != null) {
						System.out.println("Correct cn-uk link exist. Delete wrong link and ontinue.");
						wrongSchoolRank.delete();
						continue;
					}
					System.out
							.print("CN:UK " + wrongSchoolRank.getCnSchoolId() + ":" + wrongSchoolRank.getUkSchoolId());
					LxSchoolRank newSchoolRank = new LxSchoolRank();
					newSchoolRank.setCnSchoolId(correctItem.getId());
					newSchoolRank.setUkSchoolId(wrongSchoolRank.getUkSchoolId());
					newSchoolRank.setScore(wrongSchoolRank.getScore());
					newSchoolRank.setRank(wrongSchoolRank.getRank());
					wrongSchoolRank.delete();
					newSchoolRank.save();
					System.out.println(" => " + newSchoolRank.getCnSchoolId() + ":" + newSchoolRank.getUkSchoolId());
					wrongItem.delete();
				}
			} else {
				System.out.println("CORRECT ITEM NOT FOUND");
				wrongItem.setName(correctName);
				wrongItem.update();
			}
		}
	}

	/**
	 * 导入课程信息
	 * 
	 * @param file
	 * @return true if success
	 */
	public boolean importMajors(File file, Long schoolId) {
		try {
			ExcelReader reader = ExcelUtil.getReader(file);
			List<List<Object>> list = reader.read();
			for (int index = 0; index < list.size(); index++) {
				List<Object> row = list.get(index);
				if (index == 0) {
					System.out.println("row.size():" + row.size());
					if (row.size() != 12) {
						System.out.println("栏数错误：确认是否为课程信息文件");
						return false;
					}
					continue;
				}
				String schoolName = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
				String englishName = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" : row.get(1)) : "";
				String name = row.size() > 2 ? String.valueOf(row.get(2) == null ? "" : row.get(2)) : "";
				String term = row.size() > 3 ? String.valueOf(row.get(3) == null ? "" : row.get(3)) : "";
				String fee = row.size() > 4 ? String.valueOf(row.get(4) == null ? "" : row.get(4)) : "";
				String coreClass = row.size() > 5 ? String.valueOf(row.get(5) == null ? "" : row.get(5)) : "";
				String requirement = row.size() > 6 ? String.valueOf(row.get(6) == null ? "" : row.get(6)) : "";
				String development = row.size() > 7 ? String.valueOf(row.get(7) == null ? "" : row.get(7)) : "";
				String department = row.size() > 8 ? String.valueOf(row.get(8) == null ? "" : row.get(8)) : "";
				String category = row.size() > 9 ? String.valueOf(row.get(9) == null ? "" : row.get(9)) : "";
				String rank = row.size() > 10 ? String.valueOf(row.get(10) == null ? "" : row.get(10)) : "";
				String keywords = row.size() > 11 ? String.valueOf(row.get(11) == null ? "" : row.get(11)) : "";

				if (schoolName.startsWith("※")) {
					System.out.println("remark row, ignored.");
					continue;
				}

				// System.out.println(index + ": " + schoolName + " - " + name + " (" + rank +
				// ") ================>");

				LxSchool school = LxSchool.dao.findById(schoolId);
				// LxSchool school = LxSchool.dao.searchFirst("name", schoolName);
				if (!school.getName().equals(schoolName)) {
					System.out.println("英国大学名称不对应：请检查是否选择了正确的大学课程信息文件");
					return false;
				}
				LxMajor major = new LxMajor();
				major.setSchoolId(school.getId());
				englishName = englishName.replace("\n", " ");
				englishName = englishName.trim();
				major.setEnglishName(englishName);
				name = name.replace("\n", " ");
				name = name.trim();
				major.setName(name);
				major.setTerm(term);
				major.setFee(fee);
				coreClass = coreClass.replace("\n", "<br />");
				major.setCoreClass(coreClass);
				requirement = requirement.replace("\n", "<br />");
				major.setRequirement(requirement);
				development = development.replace("\n", "<br />");
				major.setDevelopment(development);
				major.setDepartment(department);
				major.setCategory(category);
				if (rank.trim().equals("本科")) {
					major.setRank(0);
				} else if (rank.trim().equals("硕士")) {
					major.setRank(1);
				} else if (rank.trim().equals("博士")) {
					major.setRank(2);
				} else if (rank.trim().equals("本科预科")) {
					major.setRank(3);
				}
				keywords = keywords.replace("\n", ";");
				major.setKeywords(keywords);
				major.save();
			}
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 批量导入公司
	 * 
	 * @param file
	 * @return true if success
	 */
	public boolean importCompany(File file) {
		try {
			ExcelReader reader = ExcelUtil.getReader(file);
			List<List<Object>> list = reader.read();
			for (int index = 0; index < list.size(); index++) {
				List<Object> row = list.get(index);
				if (index == 0) {
					if (row.size() != 2) {
						return false;
					}
					continue;
				}
				// String id = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" :
				// row.get(0)) : "";
				String name = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
				String englishName = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" : row.get(1)) : "";
				// String status = row.size() > 3 ? String.valueOf(row.get(3) == null ? "" :
				// row.get(3)) : "";

				if (name.startsWith("※")) {
					// System.out.println("remark row, ignored.");
					continue;
				}

				// System.out.println(index + ": " + name + " - " + englishName + "
				// ================>");

				LxCompany company = new LxCompany();
				// company.setId(Long.valueOf(id));
				company.setName(name);
				company.setEnglishName(englishName);
				// company.setStatus(Integer.valueOf(status));
				company.setStatus(1);
				company.save();
			}
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public void importMember() {
		String path = "D:\\UserData\\Desktop\\super_member.xlsx";
		ExcelReader reader = ExcelUtil.getReader(path);
		List<List<Object>> list = reader.read();
		for (int index = 0; index < list.size(); index++) {
			if (index == 0) {
				continue;
			}
			List<Object> row = list.get(index);
			String id = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
			String firstName = row.size() > 4 ? String.valueOf(row.get(4) == null ? "" : row.get(4)) : "";
			String lastName = row.size() > 5 ? String.valueOf(row.get(5) == null ? "" : row.get(5)) : "";
			String nickname = row.size() > 7 ? String.valueOf(row.get(7) == null ? "" : row.get(7)) : "";
			String email = row.size() > 8 ? String.valueOf(row.get(8) == null ? "" : row.get(8)) : "";
			String thumb = row.size() > 9 ? String.valueOf(row.get(9) == null ? "" : row.get(9)) : "";
			String wechatNickname = row.size() > 11 ? String.valueOf(row.get(11) == null ? "" : row.get(11)) : "";
			String wechatPortrait = row.size() > 12 ? String.valueOf(row.get(12) == null ? "" : row.get(12)) : "";
			String openId = row.size() > 13 ? String.valueOf(row.get(13) == null ? "" : row.get(13)) : "";
			String mobile = row.size() > 17 ? String.valueOf(row.get(17) == null ? "" : row.get(17)) : "";
			String city = row.size() > 20 ? String.valueOf(row.get(20) == null ? "" : row.get(20)) : "";
			String companyId = row.size() > 21 ? String.valueOf(row.get(21) == null ? "" : row.get(21)) : "";
			String name = row.size() > 22 ? String.valueOf(row.get(22) == null ? "" : row.get(22)) : "";
			// 审核状态（0：未审核（用户未提交审核信息）；1：通过审核；2：审核中；3：未通过审核）
			String isPass = row.size() > 29 ? String.valueOf(row.get(29) == null ? "" : row.get(29)) : "";
			String sex = row.size() > 31 ? String.valueOf(row.get(31) == null ? "" : row.get(31)) : "";
			String birthday = row.size() > 33 ? String.valueOf(row.get(33) == null ? "" : row.get(33)) : "";
			String qq = row.size() > 34 ? String.valueOf(row.get(34) == null ? "" : row.get(34)) : "";
			String scores = row.size() > 36 ? String.valueOf(row.get(36) == null ? "" : row.get(36)) : "";
			String userType = row.size() > 46 ? String.valueOf(row.get(46) == null ? "" : row.get(46)) : "";
			String province = row.size() > 47 ? String.valueOf(row.get(47) == null ? "" : row.get(47)) : "";
			String address = row.size() > 48 ? String.valueOf(row.get(48) == null ? "" : row.get(48)) : "";
			String remark = row.size() > 49 ? String.valueOf(row.get(49) == null ? "" : row.get(49)) : "";
			String isBlack = row.size() > 50 ? String.valueOf(row.get(50) == null ? "" : row.get(50)) : "";
			String englishName = row.size() > 51 ? String.valueOf(row.get(51) == null ? "" : row.get(51)) : "";
			String country = row.size() > 52 ? String.valueOf(row.get(52) == null ? "" : row.get(52)) : "";
			String startYear = row.size() > 53 ? String.valueOf(row.get(53) == null ? "" : row.get(53)) : "";
			String startMonth = row.size() > 54 ? String.valueOf(row.get(54) == null ? "" : row.get(54)) : "";
			// 服务年限等级（1：1年以下；2：1-2年；3：3-4年；4：5-9年；5：10-19年；6：20年以上）
			String serviceYearLevel = row.size() > 55 ? String.valueOf(row.get(55) == null ? "" : row.get(55)) : "";
			String hobby = row.size() > 60 ? String.valueOf(row.get(60) == null ? "" : row.get(60)) : "";
			String card = row.size() > 61 ? String.valueOf(row.get(61) == null ? "" : row.get(61)) : "";
			String introduction = row.size() > 62 ? String.valueOf(row.get(62) == null ? "" : row.get(62)) : "";
			String occupationClass = row.size() > 63 ? String.valueOf(row.get(63) == null ? "" : row.get(63)) : "";
			String content = row.size() > 65 ? String.valueOf(row.get(65) == null ? "" : row.get(65)) : "";
			String regStatus = row.size() > 93 ? String.valueOf(row.get(93) == null ? "" : row.get(93)) : "";

			System.out.println(index + ": " + name + " ================>");
			System.out.println("birthday:" + birthday + " mobile:" + mobile);
			System.out.println("companyId:" + companyId + " startDate:" + startYear + "-" + startMonth);
			System.out.println("companyId is number:" + NumberUtil.isNumber(companyId));

			LxUser user = new LxUser();
			user.setId(Long.valueOf(id));
			user.setFirstName(firstName);
			user.setLastName(lastName);
			user.setNickname(nickname);
			user.setEmail(email);
			user.setAvatar("http://res.superlinkplus.com/" + thumb);
			user.setWechatNickname(wechatNickname);
			user.setWechatPortrait(wechatPortrait);
			user.setOpenId(openId);
			user.setMobile(mobile);
			user.setCity(city);
			if (StrKit.notBlank(companyId) && NumberUtil.isNumber(companyId)) {
				user.setCompanyId(Long.valueOf(companyId));
			}
			user.setName(name);
			if (isPass.equals("0") || isPass.equals("2")) {
				user.setStatus(1);
			} else if (isPass.equals("1")) {
				user.setStatus(2);
			} else if (isPass.equals("3")) {
				user.setStatus(3);
			}
			user.setSex(Integer.valueOf(sex));
			if (StrKit.notBlank(birthday) && !birthday.startsWith("18") && !birthday.startsWith("190")) {
				user.setBirthday(DateUtil.parse(birthday));
			}
			user.setQq(qq);
			user.setPoint(Integer.valueOf(scores));
			user.setUserType(Integer.valueOf(userType));
			user.setProvince(province);
			user.setAddress(address);
			user.setRemark(remark);
			user.setIsBlack(Integer.valueOf(isBlack));
			user.setEnglishName(englishName);
			user.setCountry(country);
			if (StrKit.notBlank(startYear) && StrKit.notBlank(startMonth)) {
				if (startMonth.length() == 1) {
					startMonth = "0" + startMonth;
				}
				user.setStartDate(startYear + "-" + startMonth);
			}
			user.setServiceYearLevel(Integer.valueOf(serviceYearLevel));
			user.setHobby(hobby);
			user.setCard(card);
			user.setIntroduction(introduction);
			user.setOccupationClass(Integer.valueOf(occupationClass));
			user.setContent(content);
			user.save();
		}
		reader.close();
	}

	public void importCerts() {
		String path = "D:\\UserData\\Desktop\\super_certificates.xlsx";
		ExcelReader reader = ExcelUtil.getReader(path);
		List<List<Object>> list = reader.read();
		for (int index = 0; index < list.size(); index++) {
			if (index == 0) {
				continue;
			}
			List<Object> row = list.get(index);
			String id = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
			// String schoolId = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" :
			// row.get(1)) : "";
			String title = row.size() > 3 ? String.valueOf(row.get(3) == null ? "" : row.get(3)) : "";
			String imgUrl = row.size() > 4 ? String.valueOf(row.get(4) == null ? "" : row.get(4)) : "";
			String schoolName = row.size() > 5 ? String.valueOf(row.get(5) == null ? "" : row.get(5)) : "";
			String sort = row.size() > 7 ? String.valueOf(row.get(7) == null ? "" : row.get(7)) : "";
			String status = row.size() > 8 ? String.valueOf(row.get(8) == null ? "" : row.get(8)) : "";
			String startDate = row.size() > 11 ? String.valueOf(row.get(11) == null ? "" : row.get(11)) : "";
			String startTime = row.size() > 12 ? String.valueOf(row.get(12) == null ? "" : row.get(12)) : "";
			String endDate = row.size() > 13 ? String.valueOf(row.get(13) == null ? "" : row.get(13)) : "";
			String endTime = row.size() > 14 ? String.valueOf(row.get(14) == null ? "" : row.get(14)) : "";
			String showType = row.size() > 15 ? String.valueOf(row.get(15) == null ? "" : row.get(15)) : "";
			String provinces = row.size() > 16 ? String.valueOf(row.get(16) == null ? "" : row.get(16)) : "";
			String cities = row.size() > 17 ? String.valueOf(row.get(17) == null ? "" : row.get(17)) : "";
			String companies = row.size() > 18 ? String.valueOf(row.get(18) == null ? "" : row.get(18)) : "";
			String puids = row.size() > 19 ? String.valueOf(row.get(19) == null ? "" : row.get(19)) : "";

			System.out.println(index + ". " + title + " - " + schoolName + " =======================>");
			System.out.println(startDate + " " + startTime);
			System.out.println(endDate + " " + endTime);

			LxUkSchool ukSchool = LxUkSchool.dao.searchFirst("name", schoolName);
			if (ukSchool == null) {
				System.out.println("ukSchool not found");
			} else {
				System.out.println("ukSchoolName:" + ukSchool.getName() + " schoolId:" + ukSchool.getId());

				LxCertificate cert = new LxCertificate();
				cert.setId(Long.valueOf(id));
				cert.setTitle(title);
				cert.setImage("http://res.superlinkplus.com/" + imgUrl);
				cert.setSchoolId(ukSchool.getId());
				cert.setSort(Long.valueOf(sort));
				if (status.equals("-1")) {
					cert.setStatus(0);
					cert.setFlag(1);
				} else if (status.equals("1")) {
					cert.setStatus(1);
				} else {
					cert.setStatus(0);
				}
				if (StrKit.notBlank(startDate) && StrKit.notBlank(startTime)) {
					String start = DateUtil.parse(startDate).toDateStr() + " " + startTime + ":00";
					System.out.println(start);
					cert.setStartTime(DateUtil.parse(start));
				}
				if (StrKit.notBlank(endDate) && StrKit.notBlank(endTime)) {
					String end = DateUtil.parse(endDate).toDateStr() + " " + endTime + ":00";
					System.out.println(end);
					cert.setEndTime(DateUtil.parse(end));
				}
				cert.setShowType(Integer.valueOf(showType));
				cert.setProvinces(provinces);
				cert.setCities(cities);
				cert.setCompanies(companies);
				cert.setPuids(puids);
				cert.save();
			}
		}
		reader.close();
	}

	public void importCertUser() {
		String path = "D:\\UserData\\Desktop\\super_certificates_member.xlsx";
		ExcelReader reader = ExcelUtil.getReader(path);
		List<List<Object>> list = reader.read();
		for (int index = 0; index < list.size(); index++) {
			if (index == 0) {
				continue;
			}
			List<Object> row = list.get(index);
			String id = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
			String certId = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" : row.get(1)) : "";
			String uid = row.size() > 2 ? String.valueOf(row.get(2) == null ? "" : row.get(2)) : "";
			String puid = row.size() > 3 ? String.valueOf(row.get(3) == null ? "" : row.get(3)) : "";
			String status = row.size() > 7 ? String.valueOf(row.get(7) == null ? "" : row.get(7)) : "";
			String addTime = row.size() > 8 ? String.valueOf(row.get(8) == null ? "" : row.get(8)) : "";

			System.out.println(
					"id:" + id + " certId:" + certId + " uid:" + uid + " puid:" + puid + " addTime:" + addTime);
			System.out.println(new DateTime(Long.valueOf(addTime) * 1000L));

			if (status.equals("-1")) {
				System.out.println("deleted, next");
				continue;
			}

			Long addTimeStamp = Long.valueOf(addTime) * 1000L;

			LxUserCertificate userCert = new LxUserCertificate();
			userCert.setUserId(Long.valueOf(uid));
			userCert.setCertificateId(Long.valueOf(certId));
			if (addTimeStamp != 0L) {
				userCert.setAddTime(new DateTime(addTimeStamp));
			}
			userCert.setPuid(Long.valueOf(puid));
			if (status.equals("1")) {
				userCert.setStatus(1);
			} else if (status.equals("0")) {
				userCert.setStatus(0);
			} else {
				userCert.setStatus(0);
				userCert.setFlag(1);
			}
			userCert.save();
		}
		reader.close();
	}

	public void importCompanySchool() {
		String path = "D:\\UserData\\Desktop\\super_medium.xlsx";
		ExcelReader reader = ExcelUtil.getReader(path);
		List<List<Object>> list = reader.read();
		for (int index = 0; index < list.size(); index++) {
			if (index == 0) {
				continue;
			}
			List<Object> row = list.get(index);
			String schoolName = row.size() > 0 ? String.valueOf(row.get(0) == null ? "" : row.get(0)) : "";
			String companyIds = row.size() > 1 ? String.valueOf(row.get(1) == null ? "" : row.get(1)) : "";

			LxSchool school = LxSchool.dao.searchFirst("name", schoolName);
			String[] ids = companyIds.split(",");
			for (String companyId : ids) {
				LxCompanySchool cs = LxCompanySchool.dao.findById(companyId, school.getId());
				if (cs == null) {
					cs = new LxCompanySchool();
					cs.setSchoolId(school.getId());
					cs.setCompanyId(Long.valueOf(companyId));
					cs.save();
				}
			}
		}
		reader.close();
	}

}
